﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.OleDb;
using System.Reflection;
using System.Text;
using Tms.Model;
using Util.Common;
using Util.DBUtility;

namespace Tms.DAL
{
    public partial class manager
    {
        private string table_name = GlobalCache.DatabasePrefix + "manager";
                
        //是否存在该记录
        public bool Exists(int id)
        {
            StringBuilder sql = new StringBuilder();
            sql.Append("select count(1) from " + table_name);
            sql.Append(" where id=" + id);            

            return DbHelperOleDb.Exists(sql.ToString());
        }
        public bool Exists(string user_name)
        {
            StringBuilder sql = new StringBuilder();
            sql.Append("select count(1) from " + table_name);
            sql.Append(" where user_name='" + user_name.Replace("'", "''") + "'");

            return DbHelperOleDb.Exists(sql.ToString());
        }

        //增加一条记录，返回自增主键值
        public int Add(Model.manager model)
        {
            StringBuilder sql = new StringBuilder();
            StringBuilder str1 = new StringBuilder();//数据字段
            StringBuilder str2 = new StringBuilder();//数据参数

            sql.Append("insert into " + table_name + "(");

            //利用反射获取实体对象的所有公共属性
            PropertyInfo[] ppts = model.GetType().GetProperties();
            List<OleDbParameter> paras = new List<OleDbParameter>();
            
            foreach (PropertyInfo ppt in ppts)
            {
                //排除自增主键字段
                if (!ppt.Name.Equals("id"))
                {
                    //判断属性值是否为空
                    if (ppt.GetValue(model, null) != null)
                    {
                        if (ppt.Name.Equals("password"))
                        {
                            str1.Append("[" + ppt.Name + "],");//拼接字段 //password 是关键字 //20191207 lxj  
                        }
                        else
                        {
                            str1.Append(ppt.Name + ",");//拼接字段    
                        }

                        str2.Append("@" + ppt.Name + ",");//声明参数

                        if (ppt.Name.Equals("add_time"))//时间字段，特殊处理
                        {
                            OleDbParameter para = new OleDbParameter("@add_time", OleDbType.Date); 
                            para.Value = model.add_time;
                            paras.Add(para);//对参数赋值
                        }
                        else
                        {
                            paras.Add(new OleDbParameter("@" + ppt.Name, ppt.GetValue(model, null)));//对参数赋值
                        }
                    }
                }
            }

            sql.Append(str1.ToString().Trim(','));
            sql.Append(") values (");
            sql.Append(str2.ToString().Trim(','));
            sql.Append(") ");

            //执行 insert，并返回新的自增主键值
            //sql.Append(";select @@IDENTITY;"); //报错：SQL结尾处找到字符。原因可能是Access数据库不支持连续执行多条语句。
            //解决方法：改造 DbHelperOleDb.GetSingle(); 将 select @@IDENTITY 单独写一行并执行。
            object obj = DbHelperOleDb.GetSingle(sql.ToString(), paras.ToArray());
            if (obj == null)
            {
                return 0;
            }
            else
            {
                return Convert.ToInt32(obj);
            }
        }

        //更新一条记录
        public bool Update(Model.manager model)
        {
            StringBuilder sql = new StringBuilder();
            StringBuilder str1 = new StringBuilder();

            sql.Append("update " + table_name + " set ");

            //利用反射获取实体对象的所有公共属性
            PropertyInfo[] ppts = model.GetType().GetProperties();
            List<OleDbParameter> paras = new List<OleDbParameter>();
            
            foreach (PropertyInfo pi in ppts)
            {
                //排除主键字段（因为不允许更新主键）
                if (!pi.Name.Equals("id"))
                {
                    //判断属性值是否为空
                    if (pi.GetValue(model, null) != null)
                    {
                        if (pi.Name.Equals("password"))
                        {
                            str1.Append("[" + pi.Name + "]=@" + pi.Name + ",");//声明参数 //password 是关键字
                        }
                        else
                        {
                            str1.Append(pi.Name + "=@" + pi.Name + ",");//声明参数 
                        }

                        if (pi.Name.Equals("add_time"))//时间字段，特殊处理
                        {
                            OleDbParameter para = new OleDbParameter("@add_time", OleDbType.Date); 
                            para.Value = model.add_time;
                            paras.Add(para);//对参数赋值
                        }
                        else
                        {
                            paras.Add(new OleDbParameter("@" + pi.Name, pi.GetValue(model, null)));//对参数赋值
                        }
                    }
                }
            }

            sql.Append(str1.ToString().Trim(','));
            sql.Append(" where id=@id ");
            paras.Add(new OleDbParameter("@id", model.id));

            int rtn = DbHelperOleDb.ExecuteSql(sql.ToString(), paras.ToArray());
            return rtn > 0;
        }

        //删除一条记录
        public bool Delete(int id)
        {
            StringBuilder sql = new StringBuilder();
            sql.Append("delete from " + table_name);
            sql.Append(" where id=@id");
            OleDbParameter[] parameters = { new OleDbParameter("@id", OleDbType.BigInt,4)};
            parameters[0].Value = id;

            int rtn = DbHelperOleDb.ExecuteSql(sql.ToString(), parameters);
            return rtn > 0;
        }

        //将一行数据转换为实体对象
        public Model.manager DataRowToModel(DataRow row)
        {
            Model.manager model = new Model.manager();
            if (row != null)
            {
                //利用反射获取实体对象的所有公共属性
                Type modelType = model.GetType();
                for (int i = 0; i < row.Table.Columns.Count; i++)
                {
                    //根据列名获取具体属性
                    PropertyInfo ppt = modelType.GetProperty(row.Table.Columns[i].ColumnName);
                    if (ppt != null && row[i] != DBNull.Value)
                    {
                        if (ppt.CanWrite)  //20200520
                            ppt.SetValue(model, row[i], null);//设置属性值
                    }
                }
            }
            return model;
        }

        //获取一个实体对象
        public Model.manager GetModel(int id)
        {
            StringBuilder strSql = new StringBuilder();
            StringBuilder str1 = new StringBuilder();
            Model.manager model = new Model.manager();

            //利用反射获取实体对象的所有公共属性
            PropertyInfo[] ppts = model.GetType().GetProperties();
            foreach (PropertyInfo ppt in ppts)
            {
                str1.Append(ppt.Name + ",");//拼接字段
            }

            strSql.Append("select top 1 " + str1.ToString().Trim(','));
            strSql.Append(" from " + table_name);
            strSql.Append(" where id=@id");
            OleDbParameter[] parameters = { new OleDbParameter("@id", OleDbType.BigInt,4)};
            parameters[0].Value = id;
            DataTable dt = DbHelperOleDb.Query(strSql.ToString(), parameters).Tables[0];

            if (dt.Rows.Count > 0)
            {
                return DataRowToModel(dt.Rows[0]);
            }
            else
            {
                return null;
            }
        }

        //根据用户名密码返回一个实体对象
        public Model.manager GetModel(string user_name, string password)
        {
            StringBuilder sql = new StringBuilder();
            sql.Append("select id from " + table_name);
            sql.Append(" where user_name='" + user_name.Replace("'", "''") + "'");
            sql.Append(" and password='" + password + "'");
            sql.Append(" and is_lock=0");

            object obj = DbHelperOleDb.GetSingle(sql.ToString());
            if (obj != null)
            {
                return GetModel(Convert.ToInt32(obj));
            }
            return null;

        }

        //获取前几行数据
        public DataSet GetList(int topNum, string strWhere, string strOrder)
        {
            //校验
            if (strWhere == null) strWhere = "";
            if (strOrder == null) strOrder = "";

            //开始
            StringBuilder sql = new StringBuilder();
            sql.Append("select ");
            if (topNum > 0)
            {
                sql.Append(" top " + topNum.ToString());
            }
            sql.Append(" * ");
            sql.Append(" from " + table_name);

            if (strWhere.Trim() != "")
            {
                sql.Append(" where " + strWhere);
            }
            if (strOrder.Trim() != "")
            {
                sql.Append(" order by " + strOrder);
            }

            return DbHelperOleDb.Query(sql.ToString());
        }

        //获取查询分页数据
        public DataSet GetList(int pageSize, int pageIndex, string strWhere, string strOrder, out int recordCount)
        {
            StringBuilder sql = new StringBuilder();
            sql.Append("select * from " + table_name);
            if (strWhere.Trim() != "")
            {
                sql.Append(" where " + strWhere);
            }

            recordCount = Convert.ToInt32(DbHelperOleDb.GetSingle(PagingHelper.CreateCountingSql(sql.ToString())));
            return DbHelperOleDb.Query(PagingHelper.CreatePagingSql(recordCount, pageSize, pageIndex, sql.ToString(), strOrder));
        }

        //根据用户名取得Salt
        public string GetSalt(string user_name)
        {
            StringBuilder sql = new StringBuilder();
            sql.Append("select top 1 salt from " + table_name);
            sql.Append(" where user_name='" + user_name.Replace("'", "''") + "'");
            
            string salt = Convert.ToString(DbHelperOleDb.GetSingle(sql.ToString()));   
            if (string.IsNullOrEmpty(salt))
            {
                return string.Empty;
            }
            return salt;
        }
                        
    }
}